package com.dp.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.SongView;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.BookConfig;
import com.dp.util.InterUtil;
import com.dp.vo.NewsContenttitle;
import com.dp.vo.NewsExtendsChannel;
import com.dp.vo.NewsExtendsChannelTitle;
import com.dp.vo.NewsExtendsChannelTitlePK;
import com.dp.vo.SongAlbums;
import com.dp.vo.SongAlbumsongs;
import com.dp.vo.SongAlbumsongsPK;
import com.dp.vo.SongArtistalbums;
import com.dp.vo.SongArtistalbumsPK;
import com.dp.vo.SongArtists;
import com.dp.vo.SongChannelsongs;
import com.dp.vo.SongSongs;
import com.dp.vo.SongSpsongs;

@Service
@Transactional
public class SongsServiceImpl extends BaseImpl {

	/*@SuppressWarnings("unchecked")
	public List getSongsbyspcode(String roleId,String spcode, int firstrow, int maxrow) {
		String hql =null;
		// "select new com.dp.pojo.Songs(s,tsi.price,tsi.priceType,tsi.serviceCode) from SongSongs s left join (SongSpsongs spsongs ,TServiceInfo tsi) on (s.songId=spsongs.songId and spsongs.servicecode = tsi.serviceCode) where s.songId = spsongs.songId and spsongs.spcode= '"+spcode+"' order by s.dateReceived desc";
		if(roleId !=null ){
			hql = "select ss.songId,ss.isrc,ss.name,ss.copyright, ss.dateReceived,ss.dateReleased,ss.takedown,tsi.price,tsi.price_type,tsi.service_code,tsi.servicename "
				+ "from song_songs ss,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId and sps.spcode= '"
				+ spcode + "' order by ss.dateReceived desc, ss.songId desc ";
		}else{
			hql = "select ss.songId,ss.isrc,ss.name,ss.copyright, ss.dateReceived,ss.dateReleased,ss.takedown,tsi.price,tsi.price_type,tsi.service_code,tsi.servicename "
				+ "from song_songs ss,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId order by ss.dateReceived desc, ss.songId desc ";
		}
		
		List<Object[]> list = getNativeQuery(hql, firstrow, maxrow);
		List<SongSongs> result = null;
		if (list.size() > 0) {

			result = new ArrayList<SongSongs>(list.size());
			for (Object[] objects : list)
			{
				if (objects != null && objects.length > 0)
				{
					int j = 0;
					SongSongs v = new SongSongs();
					v.setSongId((Integer) objects[j++]);
					v.setIsrc((String) objects[j++]);
					v.setName((String) objects[j++]);
					v.setCopyright((String) objects[j++]);
					v.setDateReceived((Date)objects[j++]);
					v.setDateReleased((Date)objects[j++]);
					v.setTakedown((Character) objects[j++]);
					v.setPrice((Integer) objects[j++]);
					v.setPricetype((String) objects[j++]);
					v.setServicecode((String) objects[j++]);
					v.setServicename((String) objects[j++]);
					result.add(v);
				}
			}
		}

		// List list = getQueryList(hql, firstrow, maxrow);

		return result;
	}*/
	
	@SuppressWarnings("unchecked")
	public List getSongsbyspcode(String roleId,String spcode,String songname, String isrc,String takedown,String contentUse , int firstrow, int maxrow) {
		String hql =null;
		// "select new com.dp.pojo.Songs(s,tsi.price,tsi.priceType,tsi.serviceCode) from SongSongs s left join (SongSpsongs spsongs ,TServiceInfo tsi) on (s.songId=spsongs.songId and spsongs.servicecode = tsi.serviceCode) where s.songId = spsongs.songId and spsongs.spcode= '"+spcode+"' order by s.dateReceived desc";
		if(roleId !=null ){
			hql = "select ss.songId,ss.isrc,ss.name,ss.copyright, ss.dateReceived,ss.dateReleased,ss.takedown,ss.contentUse,tsi.price,tsi.price_type,tsi.service_code,tsi.servicename "
				+ "from song_songs ss,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId and sps.spcode= '"
				+ spcode + "' ";
			if (songname !=null && !"".equals(songname)){
				hql +=" and ss.name like '%"+songname+"%'";
			}
			if (isrc !=null && !"".equals(isrc)){
				hql +=" and ss.isrc like '%"+isrc+"%'";
			}
			if (takedown !=null && !"".equals(takedown)){
				hql +=" and ss.takedown ='"+takedown+"'";
			}
			if (contentUse !=null && !"".equals(contentUse)){
				hql +=" and ss.contentUse ='"+contentUse+"'";
			}
			hql +=" order by ss.dateReceived desc, ss.songId desc ";
		}else{
			hql = "select ss.songId,ss.isrc,ss.name,ss.copyright, ss.dateReceived,ss.dateReleased,ss.takedown,ss.contentUse,tsi.price,tsi.price_type,tsi.service_code,tsi.servicename "
				+ "from song_songs ss,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId";
				if (songname !=null && !"".equals(songname)){
					hql +=" and ss.name like '%"+songname+"%'";
				}
				if (isrc !=null && !"".equals(isrc)){
					hql +=" and ss.isrc like '%"+isrc+"%'";
				}
				if (takedown !=null && !"".equals(takedown)){
					hql +=" and ss.takedown ='"+takedown+"'";
				}
				if (contentUse !=null && !"".equals(contentUse)){
					hql +=" and ss.contentUse ='"+contentUse+"'";
				}
				hql +=" order by ss.dateReceived desc, ss.songId desc ";
		}
		
		List<Object[]> list = getNativeQuery(hql, firstrow, maxrow);
		List<SongSongs> result = null;
		if (list.size() > 0) {

			result = new ArrayList<SongSongs>(list.size());
			for (Object[] objects : list)
			{
				if (objects != null && objects.length > 0)
				{
					int j = 0;
					SongSongs v = new SongSongs();
					v.setSongId((Integer) objects[j++]);
					v.setIsrc((String) objects[j++]);
					v.setName((String) objects[j++]);
					v.setCopyright((String) objects[j++]);
					v.setDateReceived((Date)objects[j++]);
					v.setDateReleased((Date)objects[j++]);
					v.setTakedown((Character) objects[j++]);
					v.setContentUse((String) objects[j++]);
					v.setPrice((Integer) objects[j++]);
					v.setPricetype((String) objects[j++]);
					v.setServicecode((String) objects[j++]);
					v.setServicename((String) objects[j++]);
					result.add(v);
				}
			}
		}

		// List list = getQueryList(hql, firstrow, maxrow);

		return result;
	}
	@SuppressWarnings("unchecked")
	public List getAuditSongsbyspcode(String roleId,String spcode, int firstrow, int maxrow) {
		String hql =null;
		if(roleId !=null ){
			hql = "select ss.songId,ss.isrc,ss.name,ss.copyright, ss.dateReceived,ss.dateReleased,ss.takedown,contentUse,tsi.price,tsi.price_type,tsi.service_code,tsi.servicename "
				+ "from song_songs ss,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId and sps.spcode= '"
				+ spcode + "' and ss.takedown='Y' order by ss.dateReceived desc, ss.songId desc ";
		}else{
			hql = "select ss.songId,ss.isrc,ss.name,ss.copyright, ss.dateReceived,ss.dateReleased,ss.takedown,contentUse,tsi.price,tsi.price_type,tsi.service_code,tsi.servicename "
				+ "from song_songs ss,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId and ss.takedown='Y' order by ss.dateReceived desc, ss.songId desc ";
		}
		
		List<Object[]> list = getNativeQuery(hql, firstrow, maxrow);
		List<SongSongs> result = null;
		if (list.size() > 0) {

			result = new ArrayList<SongSongs>(list.size());
			for (Object[] objects : list)
			{
				if (objects != null && objects.length > 0)
				{
					int j = 0;
					SongSongs v = new SongSongs();
					v.setSongId((Integer) objects[j++]);
					v.setIsrc((String) objects[j++]);
					v.setName((String) objects[j++]);
					v.setCopyright((String) objects[j++]);
					v.setDateReceived((Date)objects[j++]);
					v.setDateReleased((Date)objects[j++]);
					v.setTakedown((Character) objects[j++]);
					v.setContentUse((String) objects[j++]);
					v.setPrice((Integer) objects[j++]);
					v.setPricetype((String) objects[j++]);
					v.setServicecode((String) objects[j++]);
					v.setServicename((String) objects[j++]);
					result.add(v);
				}
			}
		}

		// List list = getQueryList(hql, firstrow, maxrow);

		return result;
	}

	public List getAllSongAlbums() {
		return this.getAll(SongAlbums.class);
	}

	public List getAllSongArtists() {
		return this.getAll(SongArtists.class);
	}

	/*public List getAllSongGenre() {
		String hql = "select s from TGenre tg where tg.conentType= '2' order by tg.genreName";
		List list = getQueryList(hql);
		return list;
	}*/

	/*@SuppressWarnings("unchecked")
	public int getAllcountSongsbyspcode(String roleId, String spcode) {
		String sql =null;
		if(roleId !=null ){
			sql = "select ss.songId from song_songs ss ,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId and sps.spcode= '"
				+ spcode + "'";
		}else{
			sql = "select ss.songId from song_songs ss ,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId";
		}
		
		List count = (List) getNativeQuery(sql);
		int ss = 0;
		if (count != null && count.size() > 0) {
			ss = count.size();
		}
		return ss;
	}*/
	
	@SuppressWarnings("unchecked")
	public int getAllcountSongsbyspcode(String roleId, String spcode,String songname, String isrc,String takedown,String contentUse) {
		String sql =null;
		if(roleId !=null ){
			sql = "select ss.songId from song_songs ss ,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId and sps.spcode= '"
				+ spcode + "'";
			if (songname !=null && !"".equals(songname)){
				sql +=" and ss.name like '%"+songname+"%'";
			}
			if (isrc !=null && !"".equals(isrc)){
				sql +=" and ss.isrc like '%"+isrc+"%'";
			}
			if (takedown !=null && !"".equals(takedown)){
				sql +=" and ss.takedown ='"+takedown+"'";
			}
			if (contentUse !=null && !"".equals(contentUse)){
				sql +=" and ss.contentUse ='"+contentUse+"'";
			}
		}else{
			sql = "select ss.songId from song_songs ss ,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId";
			if (songname !=null && !"".equals(songname)){
				sql +=" and ss.name like '%"+songname+"%'";
			}
			if (isrc !=null && !"".equals(isrc)){
				sql +=" and ss.isrc like '%"+isrc+"%'";
			}
			if (takedown !=null && !"".equals(takedown)){
				sql +=" and ss.takedown ='"+takedown+"'";
			}
			if (contentUse !=null && !"".equals(contentUse)){
				sql +=" and ss.contentUse ='"+contentUse+"'";
			}
		}
		
		List count = (List) getNativeQuery(sql);
		int ss = 0;
		if (count != null && count.size() > 0) {
			ss = count.size();
		}
		return ss;
	}
	
	
	public int getAuditcountSongsbyspcode(String roleId, String spcode) {
		String sql =null;
		// "select new com.dp.pojo.Songs(s,tsi.price,tsi.priceType,tsi.serviceCode) from SongSongs s left join (SongSpsongs spsongs ,TServiceInfo tsi) on (s.songId=spsongs.songId and spsongs.servicecode = tsi.serviceCode) where s.songId = spsongs.songId and spsongs.spcode= '"+spcode+"' order by s.dateReceived desc";
		if(roleId !=null ){
			sql = "select ss.songId from song_songs ss ,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId and ss.takedown='Y' and sps.spcode= '"
				+ spcode + "'";
		}else{
			sql = "select ss.songId from song_songs ss ,song_spsongs sps left join (t_service_info tsi) on (sps.servicecode = tsi.service_code) where ss.songId = sps.songId and ss.takedown='Y' ";
		}
		
		// String hql =
		// "select count(s) from SongSongs s left join (SongSpsongs spsongs ,TServiceInfo tsi) on (s.songId=spsongs.songId and spsongs.servicecode = tsi.serviceCode) where s.songId = spsongs.songId and spsongs.spcode= '"+spcode+"'";
		List count = (List) getNativeQuery(sql);
		int ss = 0;
		if (count != null && count.size() > 0) {
			ss = count.size();
		}
		return ss;
	}

	
	public SongView getSongs(int songid) {
		// SongSongs song = (SongSongs) this.get(SongSongs.class, songid);
		
		String hql = "select new com.dp.pojo.SongView(ss.songId, ss.name, ss.copyright, ss.dateReceived,ss.dateReleased,ss.duration,ss.isrc, "
				+ "sar.artistId,sar.name, sal.albumId,sal.name,sal.upc,sps.publisherId, sps.name, ss.takedown) "
				+ "from SongSongs ss, SongArtists sar, SongAlbumsongs sas, SongAlbums sal, "
				+ "SongArtistalbums sat,SongPublishers sps "
				+ "where ss.songId = '"
				+ songid
				+ "' and ss.songId=sas.songAlbumsongsPK.songId and sas.songAlbumsongsPK.albumId=sal.albumId "
				+ "and sal.albumId=sat.songArtistalbumsPK.albumId and sat.songArtistalbumsPK.artistId = sar.artistId and sal.publisherId = sps.publisherId";
		SongView song = (SongView) getSingleResult(hql);
		return song;
	}

	public List getChannels(String employee_id, String role_id) {
		String hql = "select ch from TChannels ch WHERE 1=1 " +
				"and ch.channelType='"+BookConfig.channel_type_song+"' and ch.status='"
				+ BookConfig.channel_status_enable + "' AND (ch.functionName='"
				+ BookConfig.channel_song + "' OR ch.functionName='"
				+ BookConfig.channel_last + "')";
		if (role_id != null) {
			if (!role_id.equals("") && null != employee_id) {
				hql = "select ch from TChannels ch,TUserChannels uc WHERE 1=1 " +
						"and ch.channelType='"+BookConfig.channel_type_song+"' " +
						"and ch.channelId=uc.channelId AND ch.status='"
						+ BookConfig.channel_status_enable
						+ "' AND (ch.functionName='"
						+ BookConfig.channel_last
						+ "' OR ch.functionName='"
						+ BookConfig.channel_song
						+ "') AND uc.employeeId='" + employee_id + "'";
			}
		}
		List list = getQueryList(hql);
		return list;

	}

//	public SongSpsongs getSongSpsongs(int songid) {
//		Query query = this.em.createNamedQuery("SongSpsongs.findBySongId");
//		query.setParameter("songId", songid);
//		SongSpsongs song = (SongSpsongs) query.getSingleResult();
//		return song;
//	}

//	public void updateSongSpsongs(String[] songids, String servicecode) {
//		for (int i = 0; i < songids.length; i++) {
//			int sid = Integer.valueOf(songids[i]);
//			SongSpsongs spsongs = this.getSongSpsongs(sid);
//			spsongs.setServicecode(servicecode);
//			this.save(spsongs);
//		}
//	}

	public void assginChannel(String[] songids, String channelid) {
		for (int i = 0; i < songids.length; i++) {
			int sid = Integer.valueOf(songids[i]);
			SongChannelsongs songChannelsongs = getSongsChannel(channelid, sid);
			if(songChannelsongs ==null){
				SongChannelsongs scs = new SongChannelsongs();
				scs.setChannelid(channelid);
				scs.setSongid(sid);
				this.persist(scs);
				NewsContenttitle nct = this.getNewsContenttitle(sid);
				NewsExtendsChannelTitle nect = new NewsExtendsChannelTitle();
				nect.setIsnew(1);
				nect.setIstest(1);
				NewsExtendsChannel nec = this.getNewsExtendsChannel(channelid);
				nect.setNewsExtendsChannelTitlePK(new NewsExtendsChannelTitlePK(Integer.valueOf(nec.getChannleid()),nct.getCid()));
				nect.setContentstatus(2);
				nect.setTitleorder(0);
				this.save(nect);
			}else{
				NewsContenttitle nct = this.getNewsContenttitle(sid);
				NewsExtendsChannel nec = this.getNewsExtendsChannel(channelid);
				NewsExtendsChannelTitlePK ntpk = new NewsExtendsChannelTitlePK(nec.getChannleid(),nct.getCid());
				NewsExtendsChannelTitle nect = (NewsExtendsChannelTitle) this.get(NewsExtendsChannelTitle.class, ntpk);
				nect.setIsnew(1);
				nect.setIstest(1);
				nect.setContentstatus(2);
				nect.setTitleorder(0);
				this.save(nect);
			}
		}
	}

	public SongChannelsongs getSongsChannel(String channel_id,int songid){
		String hql = "select scs from SongChannelsongs scs where scs.songid="+songid+" and scs.channelid= '"+channel_id+"'";
		SongChannelsongs songChannelsongs = (SongChannelsongs) getSingleResult(hql);
		return songChannelsongs;
	}
	
	
	public NewsExtendsChannel getNewsExtendsChannel(String channel_id){
		String hql = "select tg from NewsExtendsChannel tg where tg.channelId='"+channel_id+"'";
		NewsExtendsChannel list = (NewsExtendsChannel) getSingleResult(hql);
		return list;
	}
	
	public NewsContenttitle getNewsContenttitle(int songid){
		String hql = "select tg from NewsContenttitle tg where tg.contentid='"+songid+"' and newstype='song'";
		NewsContenttitle list = (NewsContenttitle) getSingleResult(hql);
		return list;
	}
	public void auditAudio(String[] songids) {
		for (int i = 0; i < songids.length; i++) {
			int sid = Integer.valueOf(songids[i]);
			SongSongs song = (SongSongs)this.get(SongSongs.class, sid);
			song.setTakedown('Y');
			
			SongView songview = this.getSongs(sid);
			
			NewsContenttitle nct = this.getNewsContenttitle(sid);
			
			if(nct == null){
				log.debug("song add NewsContenttitle");
				nct = new NewsContenttitle();
			}else{
				log.debug("song add NewsContenttitle cid: "+nct.getCid());
			}
			nct.setContenticon(1);
			nct.setContentid(sid+"");
			nct.setContenttitle(song.getName());
			String content="";
			if(songview !=null){
				content = InterUtil.rb.getString("src.artist")+"："+songview.getArtistname()+"" +
				"@"+InterUtil.rb.getString("src.album")+"："+songview.getAlbumname()+"" +	
				"@"+InterUtil.rb.getString("src.songcompany")+"："+songview.getPublishname()+"" +
				"@"+InterUtil.rb.getString("src.intro")+"：";
			}
			
			
			nct.setContenttext(content);
			nct.setNewstype(InterUtil.rb.getString("src.song"));
			this.save(nct);
			this.save(song);
		}
		
	}
	
	public List getChannelsbysong(int songid){
		String hql = "select channel from SongChannelsongs scs, TChannels channel where scs.songid='"+songid+"' and channel.channelId = scs.channelid";
		List list = (List) getQueryList(hql);
		return list;
	}

	public void delsongch(String channelid, Integer sid) {
		SongChannelsongs songChannelsongs = getSongsChannel(channelid, sid);
		if(songChannelsongs !=null){
			NewsContenttitle nct = this.getNewsContenttitle(sid);
			NewsExtendsChannel nec = this.getNewsExtendsChannel(channelid);
			NewsExtendsChannelTitlePK ntpk = new NewsExtendsChannelTitlePK(nec.getChannleid(),nct.getCid());
			NewsExtendsChannelTitle nect = (NewsExtendsChannelTitle) this.get(NewsExtendsChannelTitle.class, ntpk);
			nect.setContentstatus(3);
			this.save(nect);
			this.remove(songChannelsongs);
		}
	}

	public void updateSongView(SongView oldView, SongView songview) {
		SongAlbumsongsPK sabPK = new SongAlbumsongsPK(oldView.getAlbumId(),oldView.getSongId());
		this.remove(SongAlbumsongs.class, sabPK);
		SongArtistalbumsPK sasPK = new SongArtistalbumsPK(oldView.getArtistId(),oldView.getAlbumId());
		this.remove(SongArtistalbums.class, sasPK);
		SongSongs ss = (SongSongs) this.get(SongSongs.class, oldView.getSongId());
		ss.setCopyright(songview.getCopyright());
		ss.setDateReceived(new Date());
		ss.setDateReleased(songview.getDateReleased());
		ss.setDuration(songview.getDuration());
		ss.setName(songview.getSongname());
		ss.setTakedown('N');
		SongAlbumsongs sab = new SongAlbumsongs();
		sab.setSongAlbumsongsPK(new SongAlbumsongsPK(songview.getAlbumId(),songview.getSongId()));
		SongArtistalbums sas = new SongArtistalbums();
		sas.setSongArtistalbumsPK(new SongArtistalbumsPK(songview.getArtistId(),songview.getAlbumId()));
		this.save(ss);
		this.save(sab);
		this.save(sas);
		//删除已分配的频道和contenttitle
		String sql="delete nect from news_contenttitle nc, news_extends_channel_title nect where nc.contentid="+ss.getSongId()+" and nc.cid = nect.cid and  and nc.newstype='song'";
		String content="delete nc,scs from news_contenttitle nc, song_channelsongs scs  where  nc.contentid="+ss.getSongId()+" and scs.songid = nc.contentid  and nc.newstype='song'";
		this.updateNativeQuery(sql);
		this.updateNativeQuery(content);
	}

}
